package com.show.service.grid;

import com.show.util.PageUtil;
import com.show.util.QueryUtil;
import com.show.dao.grid.CellGridRepository;
import com.show.dao.grid.GridRepositoty;
import com.show.dto.grid.CellGrid;
import com.show.dto.grid.Grid;
import com.show.dto.grid_owner.GridOwner;
import com.show.dto.population.Population;
import com.show.service.grid_owner.GridOwnerService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by yxd on 2018/1/30.
 */
@Service
@Transactional
public class GridService {
    @Autowired
    private GridRepositoty gridRepositoty;
    @Autowired
    private QueryUtil queryUtil;
    @Autowired
    private CellGridRepository cellGridRepository;
    @Autowired
    private GridOwnerService gridOwnerService;

    /**
     * 查询所有网格(分页)
     * params
     *
     * @author yxd
     * @date 2018-01-30 10:13
     */
    public PageUtil<Grid> findAllByPage(Grid grid, Integer page, Integer pageSize) {

        StringBuffer sql = new StringBuffer();
        if (null == page || "".equals(page) || "0".equals(page)) {//排空，设置默认值
            page = 1;
        }
        if (null == pageSize || "".equals(pageSize) || "0".equals(pageSize)) {//排空，设置默认值
            pageSize = 10;
        }
        sql.append(" select grid_id,grid_code,grid_name,sts,lng,lat,org_id,cdate" +
                ",(select GROUP_CONCAT(c.cell_id) from cell_grid c where c.grid_id=g.grid_id) as cellId," +
                " (select GROUP_CONCAT(e.emp_name) from grid_owner o,sys_emp e where o.grid_id=g.grid_id and e.emp_id=o.emp_id) as gridOwner" +
                " from grid g where 1=1 ");
        //拼接查询条件
        if (StringUtils.isNotBlank(grid.getGridCode())) {
            sql.append(" and grid_code like '%" + grid.getGridCode() + "%' ");
        }
        if (StringUtils.isNotBlank(grid.getGridName())) {
            sql.append(" and grid_name like '%" + grid.getGridName() + "%' ");
        }
        sql.append(" and sts='1' ");
        PageUtil<Grid> roleMenus = queryUtil.queryByPage(sql.toString(), Grid.class, page, pageSize);//分页查询
        System.out.println("查询所有网格（分页）=" + sql.toString());
        return roleMenus;
    }

    /**
     * 查询所有责任网格
     * params
     *
     * @author yxd
     * @date 2018-01-30 10:13
     */
    public List<Grid> findAll() {
        return gridRepositoty.findAll();
    }

    /**
     * 查询所有单元网格
     * params
     *
     * @author yxd
     * @date 2018-01-30 10:13
     */
    public List<Grid> findGridCell(String grid_id) {
        String sql = "select * from cell_grid c where c.grid_id='" + grid_id + "' ";
        List<Grid> list = queryUtil.query(sql.toString(), Grid.class);
        System.out.println("查询所有单元网格=" + sql.toString());
        return list;
    }

    /**
     * 查看是否有重复用户by 责任网格编号、责任网格名称
     *
     * @author yxd
     * @Param 责任网格编号、责任网格名称
     * @date 2018-1-30
     */
    public String checkEmpGrid(Grid grid) {

        String num = "";
        String str = "";

        int id = grid.getGridId();
        if (id == 0) {
            int num1 = gridRepositoty.findByGridName(grid.getGridName());
            if (num1 > 0) {
                num = "MN";
            }
            int num2 = gridRepositoty.findByGridCode(grid.getGridCode());
            if (num2 > 0) {
                num = "MC";
            }
        }
        return num;
    }

    /**
     * 增加责任网格、单元网格
     *
     * @author yxd
     * @Param 责任网格编号、责任网格名称、单元网格，所属社区
     * @date 2018-1-30
     */
    public Grid save(Grid grid) {
        Date curDate = new Date(System.currentTimeMillis());//获取当前时间
        grid.setCdate(curDate);
        grid.setSts("1");
        Grid grid1 = gridRepositoty.save(grid);
        String[] cellIds = grid.getCellId().split(",");
        ArrayList list = new ArrayList();
        for (int i = 0; i < cellIds.length; i++) {
            int num3 = gridRepositoty.findByCellId(cellIds[i]);
            if (num3 > 0) {
                cellIds[i] = "";
            }
        }
//        字符串数组去重
        for (int i = 0; i < cellIds.length; i++) {
            if (!list.contains(cellIds[i]) && cellIds[i] != "")
                list.add(cellIds[i]);
        }
        for (int i = 0; i < list.size(); i++) {
            grid.addCellGrid(new CellGrid(list.get(i).toString()));
        }
        String[] gridOwner = grid.getGridOwner().split(",");
        for (int i = 0; i < gridOwner.length; i++) {
            GridOwner info = new GridOwner();
            info.setGridId(grid1.getGridId());
            if("".equals(gridOwner[i])){
                info.setEmpId(0);
            }else{
                info.setEmpId(Integer.parseInt(gridOwner[i]));
            }
            gridOwnerService.insert(info);
        }
        return grid1;//向grid表存值

    }

    /**
     * 查询责任网格名称
     *
     * @author yxd
     * @Param 责任网格Id
     * @date 2018-1-30 8:54
     */
    public List<Grid> getGridNameByGridId(String gridId) {
        String sql = "select grid_id,grid_code,grid_name,sts,lng,lat,org_id,cdate,(select GROUP_CONCAT(c.cell_id) from cell_grid c where c.grid_id=g.grid_id) as cellId" +
                ",\n" +
                "(select GROUP_CONCAT(e.emp_name) from grid_owner o,sys_emp e where o.grid_id=g.grid_id and e.emp_id=o.emp_id) as gridOwner" +
                "  from grid g where 1=1  " +
                "                and g.grid_id='" + gridId + "' " +
                "                and sts='1' " +
                "                GROUP BY " +
                "                g.grid_name";
        List<Grid> grid = queryUtil.query(sql.toString(), Grid.class);
        return grid;
    }

    /**
     * 显示万米单元网格
     *
     * @author yxd
     * @Param 责任网格Id
     * @date 2018-1-30
     */
    public List<CellGrid> findCellId(int gridId) {
        String sql = "SELECT * FROM cell_grid g WHERE g.grid_id=" + gridId + " ";
        List<CellGrid> list = queryUtil.query(sql.toString(), CellGrid.class);
        return list;
    }

    /**
     * 查询责任网格ByID
     *
     * @author yxd
     * @Param 责任网格Id
     * @date 2018-1-30
     */
    public Grid findOneById(int gridId) {
        Grid gird = gridRepositoty.findOne(gridId);
        if(gird != null){
            return gird;
        }else{
            return null;
        }
    }

    /**
     * 修改责任网格、单元网格信息
     *
     * @author yxd
     * @Param 责任网格编号、责任网格名称、单元网格，所属社区
     * @date 2018-1-30
     */
    public Grid update(Grid grid) {
        Date curDate = new Date(System.currentTimeMillis());//获取当前时间
        if (grid.getGridId() != 0) {
            cellGridRepository.deleteCellGrid(grid.getGridId());//删除单元网格
        }
        grid.setSts("1");
        grid.setCdate(curDate);
        String[] cellIds = grid.getCellId().split(",");
        ArrayList list = new ArrayList();
        for (int i = 0; i < cellIds.length; i++) {
            int num3 = gridRepositoty.findByCellId(cellIds[i]);
            if (num3 > 0) {
                cellIds[i] = "";
            }
        }
//        字符串数组去重
        for (int i = 0; i < cellIds.length; i++) {
            if (!list.contains(cellIds[i]) && cellIds[i] != "")
                list.add(cellIds[i]);
        }
        for (int i = 0; i < list.size(); i++) {
            grid.addCellGrid(new CellGrid(list.get(i).toString()));
        }
        String[] gridOwner = grid.getGridOwner().split(",");
        gridOwnerService.delete(grid.getGridId());
        for (int i = 0; i < gridOwner.length; i++) {
            GridOwner info = new GridOwner();
            info.setGridId(grid.getGridId());
            info.setEmpId(Integer.parseInt(gridOwner[i]));
            gridOwnerService.insert(info);
        }
        return gridRepositoty.save(grid);//向grid表存值

    }

    /**
     * 删除责任网格
     *
     * @author yxd
     * @Param 责任网格ID
     * @date 2018-1-30
     */
    public int delete(String grid_id) {
        gridRepositoty.update(grid_id);
        gridRepositoty.delete(grid_id);
        return 0;
    }

    /**
     * 根据社区ID查询责任网格
     *
     * @author yxd
     * @Param 社区ID
     * @date 2018-2-9
     */
    public List<Grid> findGridByOrgId(int org_id) {
        String sql = "select * from grid where org_id=" + org_id + " ";
        List<Grid> grid = queryUtil.query(sql.toString(), Grid.class);
        return grid;
    }


    /**
     * 根据单元网格ID查询责任网格信息
     *
     * @author yxd
     * @Param 单元网格ID
     * @date 2018-2-9
     */
    public List<Grid> findGridByCellId(String cellId) {
        String sql = "SELECT\n" +
                "\tg.grid_id,\n" +
                "\tg.grid_name,\n" +
                "\tg.org_id,\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\torg_name\n" +
                "\t\tFROM\n" +
                "\t\t\tsys_org o\n" +
                "\t\tWHERE\n" +
                "\t\t\to.org_id = g.org_id\n" +
                "\t) AS orgName\n" +
                "FROM\n" +
                "\tgrid g\n" +
                "WHERE\n" +
                "\tg.grid_id = (\n" +
                "\t\tSELECT\n" +
                "\t\t\tc.grid_id\n" +
                "\t\tFROM\n" +
                "\t\t\tcell_grid c\n" +
                "\t\tWHERE\n" +
                "\t\t\tc.cell_id = '" + cellId + "'\n" +
                "\t) ";
        List<Grid> grid = queryUtil.query(sql.toString(), Grid.class);
        return grid;
    }

    /**
     * 根据网格名称反差网格id
     *
     * @author 姜文
     * @date 2018年2月27日, AM 09:25:33
     */
    public String getGridIdByGridName(String gridCode) {
        String sql = "SELECT * FROM `grid` WHERE grid_code = '" + gridCode + "'";
        List<Grid> gridList = queryUtil.query(sql.toString(), Grid.class);
        if (gridList.size() > 1 || gridList.size() == 0) {
            return "-1";
        } else {
            return String.valueOf(gridList.get(0).getGridId());
        }
    }

    /**
     * 通过单元网格id获取网格名称
     *
     * @param cellGridId
     * @return
     * @author baiyu
     */
    public List<Grid> getGridName(String cellGridId) {
        String sql = "SELECT\n" +
                "	b.building_id,g.grid_name AS gridName,oo.org_name,b.building_name\n" +
                "FROM\n" +
                "	building b\n" +
                "LEFT JOIN grid g ON b.grid_id = g.grid_id \n" +
                "LEFT JOIN cell_grid cg ON cg.grid_id = g.grid_id\n" +
                "LEFT JOIN  sys_org oo ON oo.org_id = g.org_id \n" +
                "WHERE\n" +
                "	cg.cell_id = '"+cellGridId+"'";
        List<Grid> list = queryUtil.query(sql, Grid.class);
        if (list.size() > 0) {
            return list;
        } else {
            return null;
        }
    }

//    /**
//     * 通过建筑物id获取单元信息
//     * @param id
//     * @return 单元list
//     * baiyu
//     */
//    public List<BuildingUnit> getUnits(String id){
//        String sql="SELECT\n" +
//                "	bb.building_name AS buildingName,\n" +
//                "	buu.unit_name,\n" +
//                "	bb.building_id AS buildingId,\n" +
//                "	buu.unit_id\n" +
//                "FROM\n" +
//                "	building_unit buu\n" +
//                "LEFT JOIN building bb ON buu.building_id = bb.building_id\n" +
//                "WHERE\n" +
//                "	bb.building_id = '"+id+"'";
//        List<BuildingUnit> list = queryUtil.query(sql,BuildingUnit.class);
//        return list;
//    }
//
//    /**
//     * 通过建筑物的单元Id获取楼层信息
//     * @param id
//     * @return 楼层信息
//     * baiyu
//     */
//    public List<BuildingFloor> getFloors(String id){
//        String sql="SELECT * FROM building_floor WHERE unit_id = '"+id+"'";
//        List<BuildingFloor> list = queryUtil.query(sql,BuildingFloor.class);
//        return list;
//    }
//
//    /**
//     * 通过建筑物的楼层Id获取门牌号信息
//     * @param id
//     * @return 门牌号信息
//     * baiyu
//     */
//    public List<HouseHolder> getDoorNum(String id){
//        String sql="SELECT * FROM house_holder WHERE floor_id = '"+id+"'";
//        List<HouseHolder> list = queryUtil.query(sql,HouseHolder.class);
//        return list;
//    }
//    /**
//     * 通过建筑物的户主id获取该住户的详细信息
//     * @param id
//     * @return 住户信息
//     * @author baiyu
//     */
//    public List<Population> getgetPeopleInfo(String id){
//        String sql = "SELECT * FROM population pp WHERE  pp.pid = '"+id+"'";
//        List<Population> list =queryUtil.query(sql,Population.class);
//        return list;
//    }


}
